package com.cmbt.dao.impl;

import com.cmbt.bean.Page;
import com.cmbt.bean.User;
import com.cmbt.dao.api.UserDao;
import com.cmbt.utils.DruidJdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    // commons-dbutils的操作对象
    QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());

    @Override
    public List<User> selectAllUserList() {
        try {
            String sql = "select user_id as userId,username,password,age,email from t_user";
            List<User> list = qr.query(sql, new BeanListHandler<>(User.class));
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public User selectUserByUserAcct(String userAcct) {
            String sql = "SELECT user_id as userId,user_acct as userAcct,password,username,age,email,phone,gender FROM t_user WHERE user_acct = ?";
            try {
                User user = qr.query(sql, new BeanHandler<>(User.class), userAcct);
                return user;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return null;
    }

    //单选删除
    @Override
    public void insertUser(User user) {
        String sql = "insert  into t_user(user_acct,password,username,age,email,phone,gender) values(?,?,?,?,?,?,?)";
        try {
            qr.insert(sql, new BeanHandler<>(User.class), user.getUserAcct(), user.getPassword(), user.getUsername(), user.getAge(),
                    user.getEmail(), user.getPhone(), user.getGender());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void updateUser(User user) {
        String sql = "update t_user set password=?,username=?,age=?,email=?,phone=?,gender=?where user_acct=?";
        Object[] params = {user.getPassword(), user.getUsername(), user.getAge(), user.getEmail(), user.getPhone(), user.getGender(), user.getUserAcct()};
        try {
            qr.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteUser(int user_id) {
        String sql = "delete from t_user where user_id=" + user_id;
        try {
            int update = qr.update(sql);
            System.out.println("update=" + update);
            //return null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //return null;
    }

//    //批量删除
//    @Override
//    public void delete(int user_id) {
//        //定义sql
//        String sql = "delete from t_user where user_id=" + user_id;
//        //执行sql
//        try {
//            int update = qr.update(sql);
//            System.out.println("update=" + update);
//            //return null;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        //return null;
//    }

    //分页展示,实现分页查询获得每页的数据
    public Connection con = null;
    public PreparedStatement pst = null;
    public Statement sm = null;
    public ResultSet rs = null;

    @Override
    public List<User> selectUserListByPage(Page page) {
        String sql1 = "select t_user.user_id as userId,user_acct as userAcct,password,username,age,email,phone,gender,role_name as roleName " +
                "from t_user left join t_user_role on t_user.user_id=t_user_role.user_id left join t_role on t_user_role.role_id=t_role.role_id limit ?,?";  //分页查询
        int pageSize = page.getPageSize();

            // 从哪start这一条开始
            int start = (page.getCurrentPage() - 1) * pageSize;

            List<User> userList = null;
            try {
                userList = qr.query(sql1, new BeanListHandler<User>(User.class), start,pageSize);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return userList;


    }

    @Override
    public int selectTotalPage(int pageSize) {
        int total=0,totalPage=0;
        String sql="select count(user_id) from t_user";
        try {
            Object obj=qr.query(sql,new ScalarHandler());  //总数
            total = Integer.parseInt(String.valueOf(obj));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        totalPage=total%pageSize>0?total/pageSize+1:total/pageSize;  //总页数

        return totalPage;
    }
}
